"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : Compatibility
Case Name   : 开启导入服务器端文件的权限后，将普通用户加入gs_role_copy_files群组，普通用户导入数据
Create At   : 2023/06/02
Owner       : n@ningyali
Description :
        1.开启导入服务器端文件的权限
        2.创建普通用户
        3.授予gs_role_copy_files权限
        4.普通用户创建表
        5.导入数据
Expect      :
        1.成功
        2.成功
        3.成功
        4.成功
        5.成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.COMPATIBILITY.GRAMMAR.LOAD. \
    Opengauss_Compatibility_Grammar_Load_Common import LoadData
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class LoadData0080(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.load_data = LoadData()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.cons = Constant()
        self.case_lable = 'load_data_0080'
        self.db_name = f'db_{self.case_lable}'
        self.t_name = f't_{self.case_lable}'
        self.u_name = f'u_{self.case_lable}'
        self.file_path = os.path.join('/home', self.pri_user.ssh_user)

        text1 = '-----pre_step1：创建数据文件；expect:创建成功-----'
        self.log.info(text1)
        self.data = "#1001,123,'绝对路径';#1002,456,少一层目录层级;" \
                    "#1003,147,gfadfr;"
        res = self.load_data.create_data_file(self.pri_user, self.case_lable,
                                              self.data)
        self.assertTrue(res, '执行失败:' + text1)

        text2 = '-----pre_step2：创建兼容B库；expect:创建成功-----'
        self.log.info(text2)
        res = self.load_data.create_b_db(self.pri_sh, self.db_name)
        self.assertTrue(res, '执行失败:' + text2)

    def test_load(self):
        text = '-----step1：开启导入服务器端文件的权限；expect：成功-----'
        self.log.info(text)
        sql = "alter system set enable_copy_server_files=on;"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('ALTER SYSTEM SET', res, '执行失败' + text)

        text = '-----step2：创建普通用户；expect：建表成功-----'
        self.log.info(text)
        sql = f"drop user if exists {self.u_name} cascade;" \
            f"create user {self.u_name} password " \
            f"'{macro.COMMON_PASSWD}';"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn(self.cons.CREATE_ROLE_SUCCESS_MSG, res,
                      '执行失败' + text)

        text = '-----step3：授予gs_role_copy_files权限; expect:成功-----'
        self.log.info(text)
        sql = f"grant gs_role_copy_files to {self.u_name};"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('GRANT ROLE', res, '执行失败' + text)

        text = '-----step4：进入普通用户创建表；expect：建表成功-----'
        self.log.info(text)
        sql = f"set session authorization {self.u_name} password " \
            f"'{macro.COMMON_PASSWD}';" \
            f"drop table if exists {self.t_name};" \
            f"create table {self.t_name}(" \
            f"c_01 int primary key," \
            f"c_02 number," \
            f"c_03 char(50));"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn(self.cons.CREATE_TABLE_SUCCESS, res,
                      '执行失败' + text)

        text = '------step5：导入数据；expect：成功------'
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_1.txt')
        res1 = self.load_data.load_data(
            self.pri_sh, self.db_name, self.t_name, f_path, truncate=False,
            alter=f"alter session set session authorization {self.u_name} "
            f"password '{macro.COMMON_PASSWD}';")
        rows, _ = self.common.parse_sql_query_res(res1)
        expect = [['1001', '123', '绝对路径'],
                  ['1002', '456', '少一层目录层级'],
                  ['1003', '147', 'gfadfr']]
        self.assertEqual(rows, expect, '执行失败' + text)

    def tearDown(self):
        text1 = '-----还原enable_copy_server_files为默认值；expect:成功-----'
        self.log.info(text1)
        sql = "alter system set enable_copy_server_files=off;"
        res1 = self.pri_sh.execut_db_sql(sql)

        self.log.info('-----清理环境-----')
        res2 = self.load_data.clear_env(self.pri_sh, self.pri_user,
                                        self.db_name,
                                        os.path.join(self.file_path,
                                                     f"{self.case_lable}*"))

        text3 = '-----删除用戶；expect:删除成功-----'
        self.log.info(text3)
        sql = f"drop user if exists {self.u_name} cascade;"
        res3 = self.pri_sh.execut_db_sql(sql)

        self.assertIn('ALTER SYSTEM SET', res1, '执行失败' + text1)
        self.assertTrue(res2, '执行失败:清理环境')
        self.assertIn('DROP ROLE', res3, '执行失败' + text3)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
